#!/usr/bin/python
# -*- coding: utf8 -*-
from pylons import config
from sqlalchemy import *
from sqlalchemy.orm import mapper, relation
from sqlalchemy.orm import scoped_session, sessionmaker

from sqlalchemy.ext.assignmapper import assign_mapper
from pylons.database import create_engine
from pylons.database import session_context as ctx

# Global session manager.  Session() returns the session object appropriate for the current web request.
try:
    Session = scoped_session(sessionmaker(autoflush=True, transactional=True, bind=config['pylons.g'].sa_engine))
except:
    Session = scoped_session(sessionmaker(autoflush=True, transactional=True, bind=config['pylons.g']))



# Global metadata. If you have multiple databases with overlapping table names, you'll need a metadata for each
# database.
meta = MetaData()

# Define a table.
biblio_table = Table('biblio', meta, 
    Column('id', Integer, primary_key=True),
    Column('biblio_type_id', Integer, ForeignKey('biblio_type.id'), nullable=False),
    Column('category_id', Integer, ForeignKey('category.id'), nullable=False),
    Column('owner_id', Integer, ForeignKey('user.id'), nullable=False),
    Column('private', Integer, nullable=False),
    Column('name', String(100), nullable=False, unique=True),
    Column('author', String(200)),
    Column('annote', Unicode()),
    Column('year', String(4)),
    Column('pages', Integer),
    Column('publishing', String(255)),
    Column('date_add', DateTime()),
    Column('note', Unicode()),
    Column('cover', Binary()),
    Column('place', Unicode()),
    Column('write_off', DateTime()),
    Column('reason', Unicode())
) 

biblio_type_table = Table('biblio_type', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(70), nullable=False, unique=True),
    Column('note', Unicode())
)

category_table = Table('category', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(70), nullable=False, unique=True),
    Column('note', Unicode())
)

biblio_writeoff_table = Table('biblio_writeoff', meta,
    Column('id', Integer, primary_key=True),
    Column('biblio_id', Integer, ForeignKey('biblio.id')),
    Column('user_id', Integer, ForeignKey('user.id')),
    Column('date_writeoff', DateTime(), nullable=False),
    Column('reason', Unicode(), nullable=False)
)

card_table = Table('card', meta,
    Column('id', Integer, primary_key=True),
    Column('biblio_id', Integer, ForeignKey('biblio.id')),
    Column('user_id', Integer, ForeignKey('user.id')),
    Column('order_date', DateTime(), nullable=False),
    Column('order_expiry_date', DateTime()),
    Column('signature', DateTime())
)

user_table = Table('user', meta,
    Column('id', Integer, primary_key=True),
    Column('fio', String(255), nullable=False),
    Column('pasport', String(100)),
    Column('address', String(255)),
    Column('email', String(50), unique=True),
    Column('zip', String(10)),
    Column('phone', String(255)),
    Column('photo', Binary()),
    Column('user_name', String(10), unique=True, nullable=False),
    Column('password', String(50)),
    Column('permitions', Integer(), nullable=False),
    Column('note', Unicode())
)

order_history_table = Table('order_history', meta,
    Column('id', Integer, primary_key=True),
    Column('biblio_id', Integer, ForeignKey('biblio.id')),
    Column('user_id', Integer, nullable=False),
    Column('order_date', DateTime()),
    Column('close_order', DateTime())
)
   
class Biblio(object):
    def __str__(self):
        return self.biblio

class BiblioType(object):
    def __str__(self):
        return self.biblio_type
        #return "%s [%s]" % (self.name, self.note)
    
class Category(object):
    def __str__(self):
        return self.category

class BiblioWriteoff(object):
    def __str__(self):
        return self.biblio_writeoff

class Card(object):
    def __str__(self):
        return self.card    

class User(object):
    def __str__(self):
        return self.user

class OrderHistory(object):
    def __str__(self):
        return self.order_history

mapper(BiblioType, biblio_type_table)

mapper(Category, category_table)

mapper(Biblio, biblio_table,
       properties={'biblio_type':relation(BiblioType),
                   'category':relation(Category)})

mapper(BiblioWriteoff, biblio_writeoff_table, allow_column_override=True,
      properties={'biblio':relation(Biblio),
                  'user':relation(User)})

mapper(User, user_table, allow_column_override=True, properties={'biblio':relation(Biblio)})

mapper(Card, card_table, allow_column_override=True, properties={'biblio':relation(Biblio), 'user':relation(User)})

mapper(OrderHistory, order_history_table, allow_column_override=True, properties={'biblio':relation(Biblio)})
